﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Text;

namespace Savage.DotNetLibrary.DatabaseLibrary
{
    public class Roles
    {
        public Roles(string connectionString)
        {
            _sqlDataWrapper = new SqlDataWrapper(connectionString);
        }

        private SqlDataWrapper _sqlDataWrapper;

        /// <summary>
        /// Create a new role in the database.
        /// </summary>
        /// <param name="roleName">The name of the role to create.</param>
        public void CreateRole(string roleName)
        {
            CreateRole(roleName, null);
        }

        /// <summary>
        /// Create a new role in the database.
        /// </summary>
        /// <param name="roleName">The name of the role to be created.</param>
        /// <param name="ownerName">the database user or role that is to own the new role. If no user is specified, the role will be owned by the user that executes CreateRole.</param>
        public void CreateRole(string roleName, string ownerName)
        {
            if (roleName == null)
                throw new ArgumentNullException("roleName");

            //Escape and trim the roleName and ownerName
            roleName = roleName.Trim().Replace("]", "]]");
            if (!String.IsNullOrEmpty(ownerName))
                ownerName = ownerName.Trim().Replace("]", "]]");

            //Build the SQL
            string cmdText = String.Format(CultureInfo.InvariantCulture, "CREATE ROLE [{0}]", roleName);
            if (!String.IsNullOrEmpty(ownerName))
                cmdText += String.Format(CultureInfo.InvariantCulture, " AUTHORIZATION [{0}]", ownerName);

            _sqlDataWrapper.ExecuteNonQuery(cmdText);
        }

        /// <summary>
        /// Removes a role from the database.
        /// </summary>
        /// <param name="roleName">The role to be dropped from the database.</param>
        public void DropRole(string roleName)
        {
            if (roleName == null)
                throw new ArgumentNullException("roleName");

            //Escape and trim the roleName
            roleName = roleName.Trim().Replace("]", "]]");

            //Build the SQL
            string cmdText = String.Format(CultureInfo.InvariantCulture, "DROP ROLE [{0}]", roleName.Trim());

            _sqlDataWrapper.ExecuteNonQuery(cmdText);
        }

        public string[] ListRoles()
        {
            string cmdText = "SELECT [name] FROM sys.database_principals WHERE [type]='R' AND [is_fixed_role]=0";
            return _sqlDataWrapper.ExecuteReaderToStringArray(cmdText, CommandType.Text, null, "name");
        }

        /// <summary>
        /// Adds a database user, database role, Windows login, or Windows group to a database role in the current database.
        /// </summary>
        /// <param name="roleName">The name of the database role in the current database.</param>
        /// <param name="memberName">The security account being added to the role. Can be a database user, database role, Windows login, or Windows group.</param>
        public void AddRoleMember(string roleName, string memberName)
        {
            if (roleName == null)
                throw new ArgumentNullException("roleName");

            if (memberName == null)
                throw new ArgumentNullException("memberName");

            //Escape and trim the loginName & roleName
            roleName = roleName.Trim();
            memberName = memberName.Trim();

            //Build the sql
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new System.Data.SqlClient.SqlParameter("@rolename", roleName));
            parameters.Add(new System.Data.SqlClient.SqlParameter("@membername", memberName));

            _sqlDataWrapper.ExecuteNonQuery("sp_addRoleMember", CommandType.StoredProcedure, parameters.ToArray());
        }

        public void DropRoleMember(string roleName, string memberName)
        {
            if (roleName == null)
                throw new ArgumentNullException("roleName");

            if (memberName == null)
                throw new ArgumentNullException("memberName");

            //Escape and trim the loginName & roleName
            roleName = roleName.Trim();
            memberName = memberName.Trim();

            //Build the sql
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new System.Data.SqlClient.SqlParameter("@rolename", roleName));
            parameters.Add(new System.Data.SqlClient.SqlParameter("@membername", memberName));

            _sqlDataWrapper.ExecuteNonQuery("sp_dropRoleMember", CommandType.StoredProcedure, parameters.ToArray());
        }
    }
}
